IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetProtocolDetailsByProtocolNumber]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetProtocolDetailsByProtocolNumber]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[GetProtocolDetailsByProtocolNumber]    
(    
	@protocolNumber VARCHAR(20),
	@domainUserId INTEGER = 0
)    
AS
BEGIN
SET NOCOUNT ON 

	DECLARE @protocolResourceType INTEGER
	SET @protocolResourceType = 2


	DECLARE @authorizedProtocols TABLE (ResourceId INT, AccessLevel INT)             
	INSERT INTO @authorizedProtocols                   
		SELECT * FROM fnadmqgetauthorizedresourcelist (@domainuserid, @protocolResourceType)    

	SELECT p.Id as ProtocolId,pp.id ProcessId,ap.AccessLevel FROM @authorizedProtocols  ap
	INNER JOIN Protocol p on p.fwkResourceId = ap.ResourceId
	INNER JOIN ProtocolProcess pp on pp.ProtocolId = p.Id
	WHERE
		P.ProtocolNumber = @protocolNumber
		AND 
		P.IsCurrent = 1

END

GO


